﻿CREATE SEQUENCE pca_courier.seq_orden_servicio_id;
CREATE TABLE pca_courier.orden_servicio
(
	orden_servicio_id bigint NOT NULL DEFAULT pca_global.fn_pseudo_encrypt_bigint((nextval('pca_courier.seq_orden_servicio_id'::regclass))::integer),
	sesion_id bigint NOT NULL,
	oficina_id varchar(2) NOT NULL,
	empresa_id varchar(2) NOT NULL,
	serie integer NOT NULL,
	numero integer NOT NULL,
	cliente_id bigint NOT NULL,
	contacto_id bigint,
	area_laboral varchar(50),
	direccion_recojo varchar(150),
	fecha_hora_recojo timestamp with time zone NOT NULL,	
	fecha_hora_sistema timestamp with time zone NOT NULL DEFAULT current_timestamp,	
	tipo_servicio_id varchar(2) NOT NULL,
	observaciones text,
	estado_orden_servicio_id varchar(2) NOT NULL,
	numero_consignados integer NOT NULL DEFAULT 0,
	correlativo_consignado_numerico boolean NOT NULL,
	correlativo_consignado_unq boolean NOT NULL,
	CONSTRAINT orden_servicio_id_pk PRIMARY KEY(orden_servicio_id),
	CONSTRAINT sesiones_fkey FOREIGN KEY(sesion_id)
		REFERENCES pca_log.sesiones(sesion_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT oficinas_fkey FOREIGN KEY(oficina_id,empresa_id)
		REFERENCES  pca.oficinas(oficina_id,empresa_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT clientes_fkey FOREIGN KEY(cliente_id)
		REFERENCES pca_clientes.empresas(empresa_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT contactos_fkey FOREIGN KEY(contacto_id)
		REFERENCES pca_clientes.contactos(contacto_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT tipo_servicio_fkey FOREIGN KEY(tipo_servicio_id)
		REFERENCES pca_courier.tipo_servicio(tipo_servicio_id)MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT estado_orden_servicio_fkey FOREIGN KEY(estado_orden_servicio_id)
		REFERENCES pca_courier.estado_orden_servicio(estado_orden_servicio_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT
);

CREATE TABLE pca_courier.orden_servicio_servicio
(
	orden_servicio_id bigint NOT NULL,
	servicio_id varchar(2) NOT NULL,
	CONSTRAINT orden_servicio_servicio_pk PRIMARY KEY(orden_servicio_id,servicio_id),
	CONSTRAINT orden_servicio_fkey FOREIGN KEY(orden_servicio_id)
		REFERENCES pca_courier.orden_servicio(orden_servicio_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CONSTRAINT servicio_fkey FOREIGN KEY(servicio_id)
		REFERENCES pca_courier.servicios(servicio_id) MATCH SIMPLE
		ON UPDATE RESTRICT ON DELETE RESTRICT
);

-------------------------------------------------------------------------------------------------------
-----------------------------------------------Vistas--------------------------------------------------
-------------------------------------------------------------------------------------------------------

CREATE OR REPLACE VIEW pca_courier.vw_orden_servicio AS
SELECT
	os.orden_servicio_id,
	os.sesion_id,
	os.oficina_id,
	os.empresa_id,
	os.serie,
	os.numero,
	pca_global.fn_get_strperiodonumero(os.serie,os.numero) AS strserienumero,
	os.cliente_id,
	p.nombre as cliente,
	os.contacto_id,
	os.area_laboral,
	c.nombre as contacto,
	os.direccion_recojo,
	os.fecha_hora_recojo,	
	os.fecha_hora_sistema,	
	os.tipo_servicio_id,
	os.observaciones,
	os.estado_orden_servicio_id,
	os.numero_consignados,
	os.correlativo_consignado_numerico,
	os.correlativo_consignado_unq
FROM
	pca_courier.orden_servicio os
INNER JOIN
	pca_clientes.empresas p
ON
	(os.cliente_id=p.empresa_id)
LEFT JOIN
	pca_clientes.contactos c
ON
	(os.contacto_id=c.contacto_id);

-------------------------------------------------------------------------------------------------------
----------------------------------Funciones pca_courier.orden_servicio---------------------------------
-------------------------------------------------------------------------------------------------------

--Devuelve un valor boolaneo que indica si existe o no la orden de servicio para una determinada oficina
CREATE OR REPLACE FUNCTION pca_courier.fn_existe_orden_servicio(varchar,--oficina_id $1
								varchar,--empresa_id $2
								integer,--serie $3
							        integer --numero $4
							        )RETURNS boolean AS
$$
DECLARE
	_orden_id bigint;
BEGIN
	SELECT 
		orden_servicio_id
	INTO
		_orden_id
	FROM 
		pca_courier.orden_servicio 
	WHERE 
		oficina_id = $1 AND
		empresa_id = $2 AND
		serie=$1 AND
		numero=$2;

	IF(FOUND)THEN
		RETURN true;
	ELSE
		RETURN false;
	END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_add_orden_servicio(bigint,--sesion_id $1
							     varchar,--oficina_id $2
							     varchar,--empresa_id $3
							     integer,--serie $4
							     integer,--numero $5
							     bigint,--cliente_id $6
							     bigint,--contacto_id $7
							     varchar,--area_laboral $8
							     varchar,--direccion_recojo $9
							     timestamp with time zone,--fecha_hora_recojo $10
							     varchar,--tipo_servicio_id $11
							     text,--observaciones $12
							     boolean,--correlativo_consignado_numerico $13
							     boolean,--correlativo_consignado_unq $14
							     varchar[] --servicios $15
							     )RETURNS integer AS
$$
DECLARE
	_existe_orden_servicio boolean;
	_id bigint;
	numservicios integer;--numero de filtros
	i integer;--puntero para recorrer los servicios
BEGIN
	_existe_orden_servicio := pca_courier.fn_existe_orden_servicio($2,$3,$4,$5);

	IF( NOT _existe_orden_servicio) THEN
		INSERT INTO pca_courier.orden_servicio
			(sesion_id,
			oficina_id,
			empresa_id,
			serie,
			numero,
			cliente_id,
			contacto_id,
			area_laboral,
			direccion_recojo,
			fecha_hora_recojo,
			tipo_servicio_id,
			observaciones,
			estado_orden_servicio_id,
			correlativo_consignado_numerico,
			correlativo_consignado_unq)
		VALUES
			($1,$2,$3,$4,$5,$6,$7,$8,$8,$9,$10,$11,$12,'00',$13,$14);

		_id := pca_global.fn_pseudo_encrypt_bigint((currval('pca_courier.seq_orden_servicio_id'::regclass))::integer);

		--registramos los servicios
		numservicios := array_upper($15,1);
		i := 1;

		WHILE i <= numfiltros 
		LOOP
			INSERT INTO pca_courier.orden_servicio_servicio
				(orden_servicio_id,
				servicio_id)
			VALUES
				(_id,$15[i]);
				
			i = i + 1;
		END LOOP;

		RETURN _id;
	ELSE
		RAISE EXCEPTION 'Ya existe orden de servicio N°: %',pca_global.fn_get_strperiodonumero($4,$5);
	END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_update_orden_servicio(bigint,--orden_servicio_id $1
								bigint,--sesion_id $2
								varchar,--oficina_id $3
								varchar,--empresa_id $4
								integer,--serie $5
								integer,--numero $6
								bigint,--cliente_id $7
								bigint,--contacto_id $8
								varchar,--area_laboral $9
								varchar,--direccion_recojo $10
								timestamp with time zone,--fecha_hora_recojo $11
								varchar,--tipo_servicio_id $12
								text,--observaciones $13
								boolean,--correlativo_consignado_numerico $14
								boolean,--correlativo_consignado_unq $15
								varchar[] --servicios $16
								)RETURNS void AS
$$
DECLARE
	_estado_orden_servicio_id pca_courier.orden_servicio.estado_orden_servicio_id%TYPE;
	numservicios integer;--numero de filtros
	i integer;--puntero para recorrer los servicios
BEGIN
	SELECT INTO 
		_estado_orden_servicio_id estado_orden_servicio_id 
	FROM 
		pca_courier.orden_servicio 
	WHERE 
		orden_servicio_id=$1;

	IF(_estado_orden_servicio_id='00') THEN
		UPDATE 
			pca_courier.orden_servicio
		SET
			sesion_id = $2,
			oficina_id = $3,
			empresa_id = $4,
			serie = $5,
			numero = $6,
			cliente_id = $7,
			contacto_id = $8,
			area_laboral = $9,
			direccion_recojo = $10,
			fecha_hora_recojo = $11,
			fecha_hora_sistema = current_timestamp,
			tipo_servicio_id = $12,
			observaciones = $13,
			correlativo_consignado_numerico = $14,
			correlativo_consignado_unq $15
		WHERE
			orden_servicio_id = $1;

		--limpiamos los servicios existentes
		DELETE FROM 
			pca_courier.orden_servicio_servicio
		WHERE
			orden_servicio_id = $1;

		--registramos los servicios
		numservicios := array_upper($16,1);
		i := 1;

		WHILE i <= numfiltros 
		LOOP
			INSERT INTO pca_courier.orden_servicio_servicio
				(orden_servicio_id,
				servicio_id)
			VALUES
				(_id,$16[i]);
				
			i = i + 1;
		END LOOP;
	ELSE
		RAISE EXCEPTION 'Orden de servicio no esta en RECEPCION';
	END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_update_orden_servicio_estado(bigint,--orden_servicio_id $1
								       varchar --estado_orden_servicio_id $2
								       ) RETURNS void AS
$$
BEGIN
	UPDATE 
		pca_courier.orden_servicio
	SET
		estado_orden_servicio_id = $2
	WHERE
		orden_servicio_id=$1; 
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_orden_servicio(bigint --orden_servicio_id  $1
							     ) RETURNS pca_courier.vw_orden_servicio AS
$$
DECLARE
	_row pca_courier.vw_orden_servicio%ROWTYPE;
BEGIN
	SELECT
		orden_servicio_id,
		sesion_id,
		oficina_id,
		empresa_id,
		serie,
		numero,
		strserienumero,
		cliente_id,
		cliente,
		contacto_id,
		area_laboral,
		contacto,
		direccion_recojo,
		fecha_hora_recojo,	
		fecha_hora_sistema,	
		tipo_servicio_id,
		observaciones,
		estado_orden_servicio_id,
		numero_consignados,
		correlativo_consignado_numerico,
		correlativo_consignado_unq
	INTO
		_row
	FROM
		pca_courier.vw_orden_servicio
	WHERE
		orden_servicio_id = $1;

	RETURN _row;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_resumen_estado_orden_servicio()RETURNS SETOF RECORD AS
$$
DECLARE
	_resumen RECORD;
	_resul RECORD;
	count_recepcion bigint;
	count_operaciones bigint;
	count_finalizado bigint;
	count_anulado bigint;
BEGIN
	count_recepcion := 0;
	count_operaciones := 0;
	count_finalizado := 0;
	count_anulado := 0;

	FOR _resumen IN
		SELECT 
			estado_orden_servicio_id,count(estado_orden_servicio_id) as total 
		FROM 
			pca_courier.orden_servicio 
		GROUP BY 
			estado_orden_servicio_id
	LOOP
		IF(_resumen.estado_orden_servicio_id='00')THEN
			count_recepcion := _resumen.total;
		ELSIF(_resumen.estado_orden_servicio_id='01')THEN
			count_operaciones := _resumen.total;
		ELSIF(_resumen.estado_orden_servicio_id='02')THEN
			count_finalizado := _resumen.total;
		ELSIF(_resumen.estado_orden_servicio_id='99')THEN
			count_anulado := _resumen.total;
		END IF;
	END LOOP;

	FOR _resul IN
		SELECT
			count_recepcion,
			count_operaciones,
			count_finalizado,
			count_anulado
	LOOP
		RETURN NEXT _resul;
	END LOOP;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_orden_servicio(integer,--start $1
								  integer,--limit $2
								  varchar,--oficina_id $3
								  varchar --empresa_id $4
								  )RETURNS SETOF refcursor AS
$$
DECLARE
	cursor_result refcursor;
	cursor_totalrows refcursor;
BEGIN
	OPEN cursor_result FOR
		SELECT
			orden_servicio_id,
			sesion_id,
			oficina_id,
			empresa_id,
			serie,
			numero,
			strserienumero,
			cliente_id,
			cliente,
			contacto_id,
			area_laboral,
			contacto,
			direccion_recojo,
			fecha_hora_recojo,	
			fecha_hora_sistema,	
			tipo_servicio_id,
			observaciones,
			estado_orden_servicio_id,
			numero_consignados,
			correlativo_consignado_numerico,
			correlativo_consignado_unq
		FROM
			pca_courier.vw_orden_servicio
		WHERE
			oficina_id = $3 AND
			empresa_id = $4
		ORDER BY
			serie DESC,numero DESC LIMIT $2 OFFSET $1;
	RETURN NEXT cursor_result;

	OPEN cursor_totalrows FOR
		SELECT 
			count(orden_servicio_id)
		FROM
			pca_courier.vw_orden_servicio
		WHERE
			oficina_id = $3 AND
			empresa_id = $4;
	RETURN NEXT cursor_totalrows;	
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_orden_servicio(integer,--start $1
								   integer, --limit $2
								   varchar,--oficina_id $3
								   varchar,--empresa_id $4
								   varchar[] --filtros $5
								   )RETURNS SETOF refcursor AS
$$
DECLARE
	cursor_result refcursor;
	cursor_totalrows refcursor;
	sqlfiltro varchar;
	sqlcount varchar;
BEGIN
	sqlfiltro := 
		'SELECT
			orden_servicio_id,
			sesion_id,
			oficina_id,
			empresa_id,
			serie,
			numero,
			strserienumero,
			cliente_id,
			cliente,
			contacto_id,
			area_laboral,
			contacto,
			direccion_recojo,
			fecha_hora_recojo,	
			fecha_hora_sistema,	
			tipo_servicio_id,
			observaciones,
			estado_orden_servicio_id,
			numero_consignados,
			correlativo_consignado_numerico,
			correlativo_consignado_unq
		FROM
			pca_courier.vw_orden_servicio
		WHERE oficina_id = $3 AND 
		      empresa_id = $4 AND '|| 
			pca_global.fn_get_strfiltro($3)|| 
		' ORDER BY
			serie DESC,numero DESC LIMIT '|| $2 || ' OFFSET '|| $1 ;

	sqlcount := 
		'SELECT
			count(orden_servicio_id)
		FROM
			pca_courier.vw_orden_servicio
		WHERE oficina_id = $3 AND 
		      empresa_id = $4 AND '|| 
			pca_global.fn_get_strfiltro($3);
	
	OPEN cursor_result FOR
		EXECUTE(sqlfiltro);
	RETURN NEXT cursor_result;

	OPEN cursor_totalrows FOR
		EXECUTE(sqlcount);
	RETURN NEXT cursor_totalrows;	
END;
$$
LANGUAGE plpgsql;

























































































CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_orden_servicio(integer,--start $1
								  integer, --limit $2
								  varchar --estado_orden_servicio_id $3
								  )RETURNS SETOF refcursor AS
$$
DECLARE
	cursor_result refcursor;
	cursor_totalrows refcursor;
BEGIN
	OPEN cursor_result FOR
		SELECT
			orden_servicio_id,
			sesion_id,
			serie,
			numero,
			strserienumero,
			cliente_id,
			cliente,
			contacto_id,
			area_laboral,
			contacto,
			direccion_recojo,
			fecha_hora_recojo,	
			fecha_hora_sistema,	
			tipo_servicio_id,
			observaciones,
			estado_orden_servicio_id,
			numero_consignados,
			correlativo_consignado_numerico,
			correlativo_consignado_unq,
			cambio
		FROM
			pca_courier.vw_orden_servicio
		WHERE
			cambio <> 'E' AND estado_orden_servicio_id=$3
		ORDER BY
			serie DESC,numero DESC LIMIT $2 OFFSET $1;
	RETURN NEXT cursor_result;

	OPEN cursor_totalrows FOR
		SELECT pca_tablas.fn_get_count_rows_table('pca_courier.orden_servicio');
	RETURN NEXT cursor_totalrows;	
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_orden_servicio(integer,--start $1
								   integer, --limit $2
								   varchar,--estado_orden_servicio_id $3
								   varchar[] --filtros $4
								   )RETURNS SETOF refcursor AS
$$
DECLARE
	cursor_result refcursor;
	cursor_totalrows refcursor;
	sqlfiltro varchar;
	sqlcount varchar;
BEGIN
	sqlfiltro := 
		'SELECT
			orden_servicio_id,
			sesion_id,
			serie,
			numero,
			strserienumero,
			cliente_id,
			cliente,
			contacto_id,
			area_laboral,
			contacto,
			direccion_recojo,
			fecha_hora_recojo,	
			fecha_hora_sistema,	
			tipo_servicio_id,
			observaciones,
			estado_orden_servicio_id,
			numero_consignados,
			correlativo_consignado_numerico,
			correlativo_consignado_unq,
			cambio
		FROM
			pca_courier.vw_orden_servicio
		WHERE cambio <> ''E'' AND estado_orden_servicio_id ='''|| $3 || ''' AND ' ||
			pca_global.fn_get_strfiltro($4)|| 
		' ORDER BY
			serie DESC,numero DESC LIMIT '|| $2 || ' OFFSET '|| $1 ;

	sqlcount := 
		'SELECT
			count(orden_servicio_id)
		FROM
			pca_courier.vw_orden_servicio
		WHERE cambio <> ''E'' AND estado_orden_servicio_id ='''|| $3 || ''' AND ' ||
			pca_global.fn_get_strfiltro($4);
	
	OPEN cursor_result FOR
		EXECUTE(sqlfiltro);
	RETURN NEXT cursor_result;

	OPEN cursor_totalrows FOR
		EXECUTE(sqlcount);
	RETURN NEXT cursor_totalrows;	
END;
$$
LANGUAGE plpgsql;

-------------------------------------------------------------------------------------------------------
-----------------------------Funciones pca_courier.orden_servicio_servicio-----------------------------
-------------------------------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION pca_courier.fn_get_list_orden_servicio_servicio(bigint--orden_servicio_id $1
								           )RETURNS SETOF pca_courier.orden_servicio_servicio AS
$$
DECLARE
	row_orden_servicio_servicio pca_courier.orden_servicio_servicio%ROWTYPE;
BEGIN
	FOR row_orden_servicio_servicio IN
		SELECT
			orden_servicio_id,
			servicio_id
		FROM
			pca_courier.orden_servicio_servicio
		WHERE
			orden_servicio_id=$1
	LOOP
		RETURN NEXT row_orden_servicio_servicio;
	END LOOP;
END;
$$
LANGUAGE plpgsql;